package com.common.www.service.tabulation;


import org.springframework.boot.base.jpa.service.BaseLogicService;
import com.common.www.bean.entity.tabulation.AllTabulation;
import com.common.www.repository.tabulation.AllTabulationRepository;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.*;


@Service
@Transactional
public class AllTabulationService extends BaseLogicService<AllTabulation,Long> {
    @Autowired
    private AllTabulationRepository repository;
    @Override
    public AllTabulationRepository getRepository() {
        return repository;
    }


    /**
     * 导入全系清单
     * @param workbook
     * @return
     * @throws Exception
     */
    public List<AllTabulation> importSimulate(Workbook workbook) throws Exception {
        Sheet sheet = null;
        if (workbook.getNumberOfSheets() > 0) {
            sheet = workbook.getSheetAt(0);
        }
        if (sheet == null) {
            throw new Exception("未找到导入数据,请检查文档");
        }
        List<AllTabulation> list = new ArrayList<>();

        for (int rowIndex = 2; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
//            System.out.println("第 " + rowIndex + " 行");

            String r0 = null;
            String r1 = null;
            String r2 = null;
            String r3 = null;
            String r4 = null;
            String r5 = null;
            String r6 = null;
            String r7 = null;
            String r8 = null;
            String r9 = null;
            String r10 = null;
            String r11 = null;
            String r12 = null;
            String r13 = null;
            String r14 = null;
            String r15 = null;

            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                break;
            }
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                cell.setCellType(CellType.STRING);
                String value = cell.getStringCellValue();

                if (cell.getColumnIndex() == 0)
                    r0 = value.trim();
                if (cell.getColumnIndex() == 1)
                    r1 = value.trim();
                if (cell.getColumnIndex() == 2)
                    r2 = value.trim();
                if (cell.getColumnIndex() == 3)
                    r3 = value.trim();
                if (cell.getColumnIndex() == 4)
                    r4 = value.trim();
                if (cell.getColumnIndex() == 5)
                    r5 = value.trim();
                if (cell.getColumnIndex() == 6)
                    r6 = value.trim();
                if (cell.getColumnIndex() == 7)
                    r7 = value.trim();
                if (cell.getColumnIndex() == 8)
                    r8 = value.trim();
                if (cell.getColumnIndex() == 9)
                    r9 = value.trim();
                if (cell.getColumnIndex() == 10)
                    r10 = value.trim();
                if (cell.getColumnIndex() == 11)
                    r11 = value.trim();
                if (cell.getColumnIndex() == 12)
                    r12 = value.trim();
                if (cell.getColumnIndex() == 13)
                    r13 = value.trim();
                if (cell.getColumnIndex() == 14)
                    r14 = value.trim();
                if (cell.getColumnIndex() == 15)
                    r15 = value.trim();

            }

            AllTabulation io = new AllTabulation();
            io.setCodeNumber(r0);
            String fenLeiStr = getFenLei(r1);
            if (fenLeiStr != null) {
                io.setFenLei(Integer.valueOf(fenLeiStr));
            } else {
                io.setFenLei(null); // 或者设置一个默认值
            }
            io.setName(r2);
            io.setModelCode(r3);
            io.setGongZuoNeiRong(r4);
            io.setUnit(r5);
            io.setFenXiFenLei(r6);
            io.setGongChengLiang(r7 != null && !r7.isEmpty() ? Double.parseDouble(r7) : null);
            io.setRenGongCost(r8 != null && !r8.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r8)) : null);
            io.setZhuCaiCost(r9 != null && !r9.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r9)) : null);
            io.setFuCaiCost(r10 != null && !r10.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r10)) : null);
            io.setJiXieCost(r11 != null && !r11.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r11)) : null);
            io.setQiYeCost(r12 != null && !r12.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r12)) : null);
            io.setShuiJin(r13 != null && !r13.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r13)) : null);
            io.setComprehensivePrice(r14 != null && !r14.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r14)) : null);
            io.setTotalPrice(r15 != null && !r15.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r15)) : null);
            list.add(io);
        }

        return list;
    }


    public String getFenLei(String r) {
        if (StringUtils.isEmpty(r)) {
            return null;
        }else {
            switch (r) {
                case "直流屏":
                    return "1";
                case "高压开关柜":
                    return "2";
                case "低压开关柜":
                    return "3";
                case "母线槽":
                    return "4";
                case "变压器":
                    return "5";
                case "10kv高压电缆":
                    return "6";
                case "架空线":
                    return "7";
                case "低压电缆":
                    return "8";
                case "预分支电缆":
                    return "9";
                case "电表箱":
                    return "10";
                case "配电箱":
                    return "11";
                case "电表":
                    return "12";
                case "高压桥架":
                    return "13";
                case "调试":
                    return "14";
                case "电缆通道":
                    return "15";
                case "电杆":
                    return "16";
                case "手续费":
                    return "17";
                case "措施费":
                    return "18";
                case "其他":
                    return "19";
                default:
                    return "0";
            }
        }
    }

    public static Cell createCellAddStype(Row row, Integer index, CellStyle style) {
        Cell hssfCell = row.createCell(index);
        hssfCell.setCellStyle(style);
        return hssfCell;
    }

    /**
     * 导出全系清单
     *
     * @return
     */
    public XSSFWorkbook exportQxqd() {
        XSSFWorkbook workbook = new XSSFWorkbook();
        String sheetName = "全系清单导出";
        Sheet sheet = workbook.createSheet(sheetName);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 居中
        // 创建日期格式
        CellStyle cellDateStyle = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cellDateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
        Row row0 = sheet.createRow(0);
        createCellAddStype(row0, 0, cellStyle).setCellValue(sheetName);

        HashMap hashMap = new HashMap();
        hashMap.put("flag", 1);

        List<AllTabulation> items = getListLogic(hashMap, null);
        items.sort(Comparator.comparing(AllTabulation::getFenLei));

//        CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, 14);// 下标从0开始 起始行号，终止行号， 起始列号，终
//        sheet.addMergedRegionUnsafe(titleRegion);
        //边框
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellDateStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellDateStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellDateStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellDateStyle.setBorderRight(BorderStyle.THIN);//右边框

        Row row1 = sheet.createRow(0);
        createCellAddStype(row1, 0, cellStyle).setCellValue("序号");
        createCellAddStype(row1, 1, cellStyle).setCellValue("分类");
        createCellAddStype(row1, 2, cellStyle).setCellValue("项目名称");
        createCellAddStype(row1, 3, cellStyle).setCellValue("型号");
        createCellAddStype(row1, 4, cellStyle).setCellValue("工作内容");
        createCellAddStype(row1, 5, cellStyle).setCellValue("单位");
        createCellAddStype(row1, 6, cellStyle).setCellValue("分析分类");
        createCellAddStype(row1, 7, cellStyle).setCellValue("工程量");
        createCellAddStype(row1, 8, cellStyle).setCellValue("综合单价组成明细");
        createCellAddStype(row1, 9, cellStyle).setCellValue("综合单价组成明细");
        createCellAddStype(row1, 10, cellStyle).setCellValue("综合单价组成明细");
        createCellAddStype(row1, 11, cellStyle).setCellValue("综合单价组成明细");
        createCellAddStype(row1, 12, cellStyle).setCellValue("综合单价组成明细");
        createCellAddStype(row1, 13, cellStyle).setCellValue("综合单价组成明细");
        createCellAddStype(row1, 14, cellStyle).setCellValue("综合单价");
        createCellAddStype(row1, 15, cellStyle).setCellValue("合价");
        Row row2 = sheet.createRow(1);
        createCellAddStype(row1, 0, cellStyle).setCellValue("序号");
        createCellAddStype(row1, 1, cellStyle).setCellValue("分类");
        createCellAddStype(row2, 2, cellStyle).setCellValue("项目名称");
        createCellAddStype(row2, 3, cellStyle).setCellValue("型号");
        createCellAddStype(row2, 4, cellStyle).setCellValue("工作内容");
        createCellAddStype(row2, 5, cellStyle).setCellValue("单位");
        createCellAddStype(row2, 6, cellStyle).setCellValue("分析分类");
        createCellAddStype(row2, 7, cellStyle).setCellValue("工程量");
        createCellAddStype(row2, 8, cellStyle).setCellValue("人工费");
        createCellAddStype(row2, 9, cellStyle).setCellValue("主材费(含损耗费)");
        createCellAddStype(row2, 10, cellStyle).setCellValue("辅材及其他材料费");
        createCellAddStype(row2, 11, cellStyle).setCellValue("机械费");
        createCellAddStype(row2, 12, cellStyle).setCellValue("企业管理费、利润、规费等");
        createCellAddStype(row2, 13, cellStyle).setCellValue("税金");
        createCellAddStype(row2, 14, cellStyle).setCellValue("综合单价");
        createCellAddStype(row2, 15, cellStyle).setCellValue("合价");

        CellRangeAddress cra1 = new CellRangeAddress(0, 1, 0, 0);
        CellRangeAddress cra2 = new CellRangeAddress(0, 1, 1, 1);
        CellRangeAddress cra3 = new CellRangeAddress(0, 1, 2, 2);
        CellRangeAddress cra4 = new CellRangeAddress(0, 1, 3, 3);
        CellRangeAddress cra5 = new CellRangeAddress(0, 1, 4, 4);
        CellRangeAddress cra6 = new CellRangeAddress(0, 1, 5, 5);
        CellRangeAddress cra7 = new CellRangeAddress(0, 1, 6, 6);
        CellRangeAddress cra8 = new CellRangeAddress(0, 1, 7, 7);
        CellRangeAddress cra9 = new CellRangeAddress(0, 0, 8, 13);
        CellRangeAddress cra10 = new CellRangeAddress(0, 1, 14, 14);
        CellRangeAddress cra11 = new CellRangeAddress(0, 1, 15, 15);

        //在sheet里增加合并单元格
        sheet.addMergedRegion(cra1);
        sheet.addMergedRegion(cra2);
        sheet.addMergedRegion(cra3);
        sheet.addMergedRegion(cra4);
        sheet.addMergedRegion(cra5);
        sheet.addMergedRegion(cra6);
        sheet.addMergedRegion(cra7);
        sheet.addMergedRegion(cra8);
        sheet.addMergedRegion(cra9);
        sheet.addMergedRegion(cra10);
        sheet.addMergedRegion(cra11);

        Integer rowIndex = 2;
        for (AllTabulation item : items) {
            Row row = sheet.createRow(rowIndex);
            createCellAddStype(row, 0, cellStyle).setCellValue(item.getCodeNumber());
            createCellAddStype(row, 1, cellStyle).setCellValue(getFenLeiName(item.getFenLei()));
            createCellAddStype(row, 2, cellStyle).setCellValue(item.getName());
            createCellAddStype(row, 3, cellStyle).setCellValue(item.getModelCode());
            createCellAddStype(row, 4, cellStyle).setCellValue(item.getGongZuoNeiRong());
            createCellAddStype(row, 5, cellStyle).setCellValue(item.getUnit() != null ? item.getUnit() : "");
            createCellAddStype(row, 6, cellStyle).setCellValue(item.getFenXiFenLei());
            createCellAddStype(row, 7, cellStyle).setCellValue(item.getGongChengLiang() != null ? item.getGongChengLiang() : 0);
            createCellAddStype(row, 8, cellStyle).setCellValue(item.getRenGongCost() != null ? item.getRenGongCost().doubleValue() : 0);
            createCellAddStype(row, 9, cellStyle).setCellValue(item.getZhuCaiCost() != null ? item.getZhuCaiCost().doubleValue() : 0);
            createCellAddStype(row, 10, cellStyle).setCellValue(item.getFuCaiCost() != null ? item.getFuCaiCost().doubleValue() : 0);
            createCellAddStype(row, 11, cellStyle).setCellValue(item.getJiXieCost() != null ? item.getJiXieCost().doubleValue() : 0);
            createCellAddStype(row, 12, cellStyle).setCellValue(item.getQiYeCost() != null ? item.getQiYeCost().doubleValue() : 0);
            createCellAddStype(row, 13, cellStyle).setCellValue(item.getShuiJin() != null ? item.getShuiJin().doubleValue() : 0);
            createCellAddStype(row, 14, cellStyle).setCellValue(item.getComprehensivePrice() != null ? item.getComprehensivePrice().doubleValue() : 0);
            createCellAddStype(row, 15, cellStyle).setCellValue(item.getTotalPrice() != null ? item.getTotalPrice().doubleValue() : 0);
            rowIndex++;
        }
        return workbook;
    }



    public String getFenLeiName(Integer fenLei) {
        if (fenLei == null) {
            return "未知";
        }
        switch (fenLei) {
            case 1:
                return "直流屏";
            case 2:
                return "高压开关柜";
            case 3:
                return "低压开关柜";
            case 4:
                return "母线槽";
            case 5:
                return "变压器";
            case 6:
                return "10kv高压电缆";
            case 7:
                return "架空线";
            case 8:
                return "低压电缆";
            case 9:
                return "预分支电缆";
            case 10:
                return "电表箱";
            case 11:
                return "配电箱";
            case 12:
                return "电表";
            case 13:
                return "高压桥架";
            case 14:
                return "调试";
            case 15:
                return "电缆通道";
            case 16:
                return "电杆";
            case 17:
                return "手续费";
            case 18:
                return "措施费";
            case 19:
                return "其他";
        }
        return "未知";
    }


}
